﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{ /// <summary>
  /// 美食表
  /// </summary>
    public class foodDAL
    {
        //美食添加
        public static int foodAdd(Food f)
        {
            string sql = "insert into tb_Food values(@FoodName,@FoodTypeID,@PicUrl,@Price,@Fen,@SalesVolume,@Inventory,@Special,@Time,@Describe,@UserID)";
            SqlParameter[] para = {
            new SqlParameter("@FoodName",f.FoodName),
            new SqlParameter("@FoodTypeID",f.FoodTypeID),
            new SqlParameter("@PicUrl",f.PicUrl),
            new SqlParameter("@Price",f.Price),
            new SqlParameter("@Fen",f.Fen),
            new SqlParameter("@SalesVolume",f.SalesVolume),
            new SqlParameter("@Inventory",f.Inventory),
            new SqlParameter("@Special",f.Special),
            new SqlParameter("@Time",f.Time),
            new SqlParameter("@Describe",f.Describe),
            new SqlParameter("@UserID",f.UserID),
            };
            return SqlDBHelper.ExecuteNonQuery(sql, para);
        }
        //美食查询
        public static DataTable selectFood(int id)
        {
            string sql = @"select f.FoodID,f.FoodName,ft.FoodTypeName,f.Describe,f.PicUrl,f.Price,f.Fen,f.Special,
            f.SalesVolume,f.Inventory from tb_Food f,tb_FoodType ft,tb_User u
            where f.FoodTypeID=ft.FoodTypeID and f.UserID=u.UserID and f.UserID=" + id;
            return SqlDBHelper.GetDataTable(sql);
        }
        //根据不同的美食模糊查询
        public static DataTable FoodMH(string sq,int id)
        {
            string sql = @"select f.FoodID,f.FoodName,ft.FoodTypeName,f.Describe,f.PicUrl,f.Price,f.Fen,f.Special,
f.SalesVolume,f.Inventory from tb_Food f,tb_FoodType ft 
where f.FoodTypeID=ft.FoodTypeID and f.UserID=" + id+"" + sq;
            return SqlDBHelper.GetDataTable(sql);
        }
        //前台搜索美食模糊查询
        public static DataTable foodHome(string foodname)
        {
            string sql = "select f.FoodID,f.FoodName,ft.FoodTypeName,f.Describe,f.PicUrl,f.Price,f.Fen,f.Special,f.SalesVolume,f.Inventory from tb_Food f,tb_FoodType ft where f.FoodTypeID=ft.FoodTypeID and FoodName like '%"+foodname+ "%'";
            return SqlDBHelper.GetDataTable(sql);
        }
        //查询数据条数
        public static object dataNum(int id)
        {
            string sql = "select count(*) from tb_Food where UserID="+id;
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //美食添加判断重复
        public static object foodCF(string name)
        {
            string sql = "select * from tb_Food where FoodName='" + name + "'";
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //美食删除
        public static int delete(int id)
        {
            string sql = "delete from tb_Food where FoodID=" + id;
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
        //根据id查询
        public static DataTable selectId(int id) {
            string sql = "select * from tb_Food f,tb_FoodType ft where f.FoodTypeID=ft.FoodTypeID and f.FoodID="+id;
            return SqlDBHelper.GetDataTable(sql);
        }
        //修改菜品
        public static int updateFood(Food f)
        {
            string sql = "update tb_Food set FoodName=@FoodName,FoodTypeID=@FoodTypeID,PicUrl=@PicUrl,Price=@Price,Fen=@Fen,SalesVolume=@SalesVolume,Inventory=@Inventory,Special=@Special,Time=@Time,Describe=@Describe where FoodID=@FoodID";
            SqlParameter[] para = {
            new SqlParameter("@FoodID",f.FoodID),
            new SqlParameter("@FoodName",f.FoodName),
            new SqlParameter("@FoodTypeID",f.FoodTypeID),
            new SqlParameter("@PicUrl",f.PicUrl),
            new SqlParameter("@Price",f.Price),
            new SqlParameter("@Fen",f.Fen),
            new SqlParameter("@SalesVolume",f.SalesVolume),
            new SqlParameter("@Inventory",f.Inventory),
            new SqlParameter("@Special",f.Special),
            new SqlParameter("@Time",f.Time),
            new SqlParameter("@Describe",f.Describe),
            };
            return SqlDBHelper.ExecuteNonQuery(sql,para);
        }
        //根据id查询销售量和库存
        public static DataTable xskc(int id) {
            string sql = "select SalesVolume,Inventory from tb_Food where FoodID=" + id;
            return SqlDBHelper.GetDataTable(sql);
        }
        //修改销售量和库存
        public static int updateKCXL(int x,int k,int id)
        {
            string sql = "update tb_Food set SalesVolume="+x+ ",Inventory=" + k + " where FoodID=" + id;
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
        //修改销售量和库存
        public static int cartKCSL(int id)
        {
            string sql = @"update tb_Food set SalesVolume+=(select number from tb_Cart where CartID=@id),
                            Inventory-=(select number from tb_Cart where CartID=@id) where FoodID=
                            (select FoodID from tb_Cart where CartID=@id)";
            SqlParameter[] para = { 
            new SqlParameter("@id",id)
            };
            return SqlDBHelper.ExecuteNonQuery(sql,para);
        }
        //前台
        //为你推荐
        public static DataTable qtWntj() {
            string sql = "select top 8 * from tb_Food order by SalesVolume desc";
            return SqlDBHelper.GetDataTable(sql);
        }
        //折扣商品
        public static DataTable qtZK()
        {
            string sql = "select top 8 * from tb_Food where Special='是'";
            return SqlDBHelper.GetDataTable(sql);
        }
        //购买商品的价格
        public static object prices(int id) {
            string sql = "select Price from tb_Food where FoodID=" + id;
            return SqlDBHelper.ExecuteScalar(sql);
        }

        /// <summary>
        /// 美食页面 主食
        /// </summary>
        /// <returns></returns>
        public static DataTable Staple()
        {
            string sql = "select * from tb_Food where FoodTypeID=1";
            return SqlDBHelper.GetDataTable(sql);
        }
        //凉菜
        public static DataTable Cold()
        {
            string sql = "select * from tb_Food where FoodTypeID=2";
            return SqlDBHelper.GetDataTable(sql);
        }
        //汤
        public static DataTable Soup()
        {
            string sql = "select * from tb_Food where FoodTypeID=3";
            return SqlDBHelper.GetDataTable(sql);
        }
        //地方菜系
        public static DataTable Regional()
        {
            string sql = "select top 8 * from tb_Food where FoodTypeID=4";
            return SqlDBHelper.GetDataTable(sql);
        }
        //甜点
        public static DataTable Dessert()
        {
            string sql = "select * from tb_Food where FoodTypeID=5";
            return SqlDBHelper.GetDataTable(sql);
        }
        //火锅
        public static DataTable Hot()
        {
            string sql = "select * from tb_Food where FoodTypeID=6";
            return SqlDBHelper.GetDataTable(sql);
        }
        //烧烤
        public static DataTable Barbecue()
        {
            string sql = "select * from tb_Food where FoodTypeID=7";
            return SqlDBHelper.GetDataTable(sql);
        }
        //海鲜
        public static DataTable Seafood()
        {
            string sql = "select * from tb_Food where FoodTypeID=8";
            return SqlDBHelper.GetDataTable(sql);
        }
        //饮料
        public static DataTable Drink()
        {
            string sql = "select * from tb_Food where FoodTypeID=9";
            return SqlDBHelper.GetDataTable(sql);
        }
        //美食销售数量和库存
        public static int upXSKC(Food f,int id)
        {
            string sql = "update tb_Food set SalesVolume=@SalesVolume,Inventory=@Inventory where FoodID=(select FoodID from tb_Cart where UserID=" + id+")";
            SqlParameter[] para = {
            new SqlParameter("@SalesVolume",f.SalesVolume),
            new SqlParameter("@Inventory",f.Inventory)
            };
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
    }
}
